package com.example.teaching_server.repository;

import com.example.teaching_server.entity.NoticeEntity;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * 通知数据访问层
 */
@Repository
public interface NoticeRepository extends JpaRepository<NoticeEntity, Long> {

    /**
     * 根据创建者查找通知（按创建时间降序排序）
     */
    List<NoticeEntity> findByCreatedByOrderByCreatedAtDesc(Long createdBy);

    /**
     * 根据类型和创建者查找通知（按创建时间降序排序）
     */
    List<NoticeEntity> findByTypeIdAndCreatedByOrderByCreatedAtDesc(Long typeId, Long createdBy);

    /**
     * 查找用户接收到的通知
     */
    @Query(value = "SELECT DISTINCT n.* FROM notices n " +
            "JOIN notice_recipients nr ON n.notice_id = nr.notice_id " +
            "WHERE ((nr.recipient_type = 'class' AND :userId IN " +
            "        (SELECT u.id FROM users u WHERE u.class_id = CAST(nr.target_id AS bigint))) " +
            "   OR (nr.recipient_type = 'user' AND CAST(nr.target_id AS bigint) = :userId) " +
            "   OR (nr.recipient_type = 'role' AND :userId IN " +
            "        (SELECT u.id FROM users u WHERE u.role = CAST(nr.target_id AS text)))) " +
            "ORDER BY n.created_at DESC",
            nativeQuery = true)
    List<NoticeEntity> findReceivedNoticesByUserId(@Param("userId") Long userId);

    /**
     * 查找用户接收到的特定类型的通知
     */
    @Query(value = "SELECT DISTINCT n.* FROM notices n " +
            "JOIN notice_recipients nr ON n.notice_id = nr.notice_id " +
            "WHERE n.type_id = :typeId " +
            "AND ((nr.recipient_type = 'class' AND :userId IN " +
            "        (SELECT u.id FROM users u WHERE u.class_id = CAST(nr.target_id AS bigint))) " +
            "   OR (nr.recipient_type = 'user' AND CAST(nr.target_id AS bigint) = :userId) " +
            "   OR (nr.recipient_type = 'role' AND :userId IN " +
            "        (SELECT u.id FROM users u WHERE u.role = CAST(nr.target_id AS text)))) " +
            "ORDER BY n.created_at DESC",
            nativeQuery = true)
    List<NoticeEntity> findReceivedNoticesByUserIdAndType(@Param("userId") Long userId,
                                                          @Param("typeId") Long typeId);

    /**
     * 根据班级ID查找通知（限制数量）
     */
    @Query(value = "SELECT n.* FROM notices n " +
            "JOIN notice_recipients nr ON n.notice_id = nr.notice_id " +
            "WHERE nr.recipient_type = 'class' AND nr.target_id = :classId " +
            "ORDER BY n.created_at DESC LIMIT :limit",
            nativeQuery = true)
    List<NoticeEntity> findByRecipientClassId(@Param("classId") Long classId, @Param("limit") int limit);

    /**
     * 查询最新的N个通知
     */
    @Query(value = "SELECT * FROM notices ORDER BY created_at DESC LIMIT :limit", nativeQuery = true)
    List<NoticeEntity> findTopNByOrderByCreatedAtDesc(@Param("limit") int limit);
}
